<?php

	session_start();
	if(!($_SESSION["username"])||($_SESSION["role"]!="TUT")){
		//not logged in or tutor
		header("location:index.php");
	}

	$objConnect = mysql_connect("localhost","root","") or die(mysql_error());
	$objDB = mysql_select_db("Project 5.5");
	$strSQL = "SELECT * FROM users WHERE Role = 'STU'";
	$objQuery = mysql_query($strSQL);
	if($objQuery){
		// Get Document Path 
		$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"])))."/temp";

		//Excel Document Root
		$filename = "Students_" . date('d-m-Y') . ".xls";

		// Connect to Excel.Application 
		$xlApp = new COM("Excel.Application");
		$xlBook = $xlApp->Workbooks->Add();
		
		$xlBook->Worksheets(1)->Select;

		// Width & Height (A1:H1)
		$xlApp->ActiveSheet->Range("A1:A1")->ColumnWidth = 10.0;
		$xlApp->ActiveSheet->Range("B1:B1")->ColumnWidth = 13.0;
		$xlApp->ActiveSheet->Range("C1:C1")->ColumnWidth = 13.0;
		$xlApp->ActiveSheet->Range("D1:D1")->ColumnWidth = 14.0;
		$xlApp->ActiveSheet->Range("E1:E1")->ColumnWidth = 12.0;
		$xlApp->ActiveSheet->Range("F1:F1")->ColumnWidth = 28.0;
		$xlApp->ActiveSheet->Range("G1:G1")->ColumnWidth = 10.0;
		$xlApp->ActiveSheet->Range("H1:H1")->ColumnWidth = 10.0;
		
		$xlApp->ActiveSheet->Range("A1:H1")->HorizontalAlignment = -4108;

		//Header Row
		$xlApp->ActiveSheet->Cells(1,1)->Value = "User_ID";
		$xlApp->ActiveSheet->Cells(1,1)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,2)->Value = "First_Name";
		$xlApp->ActiveSheet->Cells(1,2)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,3)->Value = "Last_Name";
		$xlApp->ActiveSheet->Cells(1,3)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,4)->Value = "Username";
		$xlApp->ActiveSheet->Cells(1,4)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,5)->Value = "Password";
		$xlApp->ActiveSheet->Cells(1,5)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,6)->Value = "Email";
		$xlApp->ActiveSheet->Cells(1,6)->Font->Bold = True;
		
		$xlApp->ActiveSheet->Cells(1,7)->Value = "Class";
		$xlApp->ActiveSheet->Cells(1,7)->Font->Bold = True;

		$xlApp->ActiveSheet->Cells(1,8)->Value = "Group";
		$xlApp->ActiveSheet->Cells(1,8)->Font->Bold = True;

		$intRows = 2;
		while($objResult = mysql_fetch_array($objQuery)){	//For each row in database
			
			$sql = "SELECT * FROM studentinfo WHERE User_ID = '".$objResult["User_ID"]."'";
			$result = mysql_fetch_row(mysql_query($sql));
			
			// enter student details
			$xlApp->ActiveSheet->Cells($intRows,1)->Value = $objResult["User_ID"];
			$xlApp->ActiveSheet->Cells($intRows,1)->HorizontalAlignment = -4131; 
			$xlApp->ActiveSheet->Cells($intRows,2)->Value = $objResult["First_Name"];
			$xlApp->ActiveSheet->Cells($intRows,2)->HorizontalAlignment = -4131; 
			$xlApp->ActiveSheet->Cells($intRows,3)->Value = $objResult["Last_Name"];
			$xlApp->ActiveSheet->Cells($intRows,3)->HorizontalAlignment = -4131; 
			$xlApp->ActiveSheet->Cells($intRows,4)->Value = $objResult["Username"];
			$xlApp->ActiveSheet->Cells($intRows,4)->HorizontalAlignment = -4131; 
			$xlApp->ActiveSheet->Cells($intRows,5)->Value = $objResult["Password"];
			$xlApp->ActiveSheet->Cells($intRows,5)->HorizontalAlignment = -4131; 
			$xlApp->ActiveSheet->Cells($intRows,6)->Value = $objResult["Email"];
			$xlApp->ActiveSheet->Cells($intRows,6)->HorizontalAlignment = -4131;
			$xlApp->ActiveSheet->Cells($intRows,7)->Value = $result[1];
			$xlApp->ActiveSheet->Cells($intRows,7)->HorizontalAlignment = -4131;
			$xlApp->ActiveSheet->Cells($intRows,8)->Value = $result[2];
			$xlApp->ActiveSheet->Cells($intRows,8)->HorizontalAlignment = -4131;

			$intRows++;
		}

		@unlink("./temp/".$filename);

		$xlBook->SaveAs($strPath."/".$filename);

		//prompt download
		header("Content-Disposition: attachment; filename=\".$filename\"");
		header("Content-Type: application/vnd.ms-excel");
		readfile('temp/'.$filename);


		//Close & Quit
		$xlApp->Application->Quit();
		$xlApp = null;
		$xlBook = null;
		$xlSheet1 = null;

		@unlink("./temp/".$filename); 

	}

	mysql_close($objConnect);
?>
